package com.cpimex.eprice.model;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import javax.activation.MimetypesFileTypeMap;

import com.cpimex.eprice.bean.DocumentoBean;


public class Upload {
	private static final String STR_DETFORMAT = "00000000";
	private static final String STR_DOCFORMAT = "00";
	
	
	/**
	 * 
	 * 
	 * @param str_detalle
	 * @param str_cliente
	 * @param str_producto
	 * @param byte_archivo
	 * @param str_file
	 * @throws FileNotFoundException
	 * @throws IOException
	 * @throws SQLException 
	 */
	public static int fileWrite(	
							Connection conn,
							String str_filePath,
							String str_detalle, 
							String str_cliente,
							String str_producto,
							String str_descripcion,
							String str_nombre,
							byte[] byte_archivo) throws FileNotFoundException, IOException, SQLException{
		
		// Formato archivo 
		// 00000000-101001-M83742-01
		// detalle - cliente - producto - numero_doc
		NumberFormat nf_det = new DecimalFormat(STR_DETFORMAT);
		NumberFormat nf_doc = new DecimalFormat(STR_DOCFORMAT);
		
		String str_documento = nf_doc.format(countDoc(conn.createStatement(), str_detalle ));
		str_detalle = nf_det.format(Integer.parseInt(str_detalle));
		
		
		String str_extension = str_nombre.substring(str_nombre.indexOf("."), str_nombre.length());
		
		//String str_file_name = str_filePath + str_detalle + "-" + str_cliente + "-" + str_producto+"-"+str_documento+  str_extension;
		//String str_nombreArchivo = str_filePath  + str_detalle + "-" + str_cliente + "-" + str_producto+"-"+str_documento + str_extension;
		
		String str_file_name =  "D:\\Inetpub\\wwwroot\\Apps\\EPrice\\Archivo\\" + str_detalle + "-" + str_cliente + "-" + str_producto+"-"+str_documento+str_extension;
		String str_nombreArchivo = "D:\\Inetpub\\wwwroot\\Apps\\EPrice\\Archivo\\" + str_detalle + "-" + str_cliente + "-" + str_producto+"-"+str_documento+str_extension;
		
		//System.out.println(str_file_name);
		
		File file = new File(str_file_name);
		
		FileOutputStream ostr_file = new FileOutputStream(file);
		
		ostr_file.write(byte_archivo);
		ostr_file.close();
		
		
		String str_add = 
			" INSERT INTO ep_documentoSolicitud(                      " +
			"                                   descripcion_docSol,   " +
			"                                   numero_docSol,        " +
			"                                   nombre_docSol,        " +
			"                                   id_detSol,            " +
			"                                   nombreInterno_docSol  " +
			"                                  )                      " +
			" VALUES(                                                 " +
			" '"+str_descripcion+"',                                  " +
			" '"+str_documento+"',                                    " +
			" '"+str_nombreArchivo+"',                                " +
			" "+Integer.parseInt(str_detalle)+",                      " +
			" '"+str_nombre+"'                                      ) " ;                                                  
		// System.out.println(str_add);
		
		int int_insert =0;
		Statement stmt = conn.createStatement();
		int_insert = stmt.executeUpdate(str_add);
		
		return int_insert;
	}
	
	
	/**
	 * 
	 * 
	 * @param stmt
	 * @param str_detalle
	 * @return int
	 * @throws SQLException
	 */
	private static int countDoc(
			Statement stmt, 
			String str_detalle) throws SQLException{
		
		int int_i = 0; 
		String str_sql = 
				" SELECT COUNT(*) as Total            " + 
			 	" FROM ep_documentoSolicitud          " +
			 	" WHERE id_detSol = '"+str_detalle+"' ";
		ResultSet rs = stmt.executeQuery(str_sql);
		if(rs.next()){
			int_i = rs.getInt("Total");
			int_i++;
		}
		return int_i;
	}
	
	
	public static ArrayList list(
			Connection conn, 
			String str_detalle) throws Exception{
		
		Statement stmt = conn.createStatement();
		
		ArrayList arrL_list = new ArrayList();
		String str_list = 
			" SELECT descripcion_docSol,               " +
			"        numero_docSol,                    " +
			"        nombre_docSol,                    " +
			"        id_detSol,                        " +
			"        nombreInterno_docSol              " +
			" FROM     ep_documentoSolicitud           " +    
			" WHERE   id_detSol = '"+str_detalle+"'    " ;
		
		ResultSet rs = stmt.executeQuery(str_list);
		while(rs.next()){
			
			String str_descripcion   = rs.getString("descripcion_docSol");
			int int_documento        = rs.getInt("numero_docSol");
			String str_nombre        = rs.getString("nombre_docSol");
			int int_detalle          = rs.getInt("id_detSol");
			String str_nombreInterno = rs.getString("nombreInterno_docSol");
			
			Map map_parametro = new HashMap();
			map_parametro.put("str_detalle",      str_detalle);
			map_parametro.put("int_documento",      new Integer(int_documento));
			
			DocumentoBean bn_documento = new DocumentoBean(str_descripcion, int_documento, str_nombre, int_detalle,str_nombreInterno, map_parametro);
			arrL_list.add(bn_documento);
		}
		return arrL_list;
	}
	
	
	public static int delete(
			Connection conn, 
			int int_documento, 
			String str_detalle) throws SQLException, FileNotFoundException{
		String str_nombreDoc = "";
		
		String str_list = 
			" SELECT descripcion_docSol,               " +
			"        numero_docSol,                    " +
			"        nombre_docSol,                    " +
			"        id_detSol,                        " +
			"        nombreInterno_docSol              " +
			" FROM     ep_documentoSolicitud           " +    
			" WHERE   id_detSol = '"+str_detalle+"'    " +
			" AND numero_docSol = "+int_documento+"    " ;
		
		System.out.print(str_list);
		Statement stmt_list = conn.createStatement();
		ResultSet rs_list = stmt_list.executeQuery(str_list);
		
		if(rs_list.next())
			str_nombreDoc = rs_list.getString("nombre_docSol");
		
		System.out.print(str_nombreDoc);
		File file = new File(str_nombreDoc);
		boolean bol_exist = file.exists();
		System.out.print(bol_exist);
		if(bol_exist)
			file.delete();
		else
			return 0;
		
		int int_delete;
		String str_delete = 
			" DELETE FROM ep_documentoSolicitud              " +
			" WHERE numero_docSol = "+ int_documento +" AND  " +
			" 		id_detSol =  '"+str_detalle+"'           ";
		
		Statement stmt = conn.createStatement();
		int_delete = stmt.executeUpdate(str_delete);
		
		return int_delete;
	}
	
	public static Map download(Connection conn, String str_detalle, String str_documento) throws SQLException, IOException{
		byte[] byte_file = null;
		
		Statement stmt = conn.createStatement();
		String str_docsol = "";
		String str_internodocsol =  "";
		String str_download = 
			" SELECT  nombre_docsol,                     " +
			"         nombreinterno_docsol" +
			" FROM ep_documentoSolicitud                " +
			" WHERE numero_docsol = '"+str_documento+"' " +
			"       AND id_detSol = '"+str_detalle+"'   ";
		
		ResultSet rs = stmt.executeQuery(str_download);
		if(rs.next()){
			str_docsol = rs.getString("nombre_docsol");
			str_internodocsol = rs.getString("nombreinterno_docsol");
		}
		
		File file = new File(str_docsol);
		Long long_size = new Long(file.length());
		String mimeType = new MimetypesFileTypeMap().getContentType(file);
		int int_size = long_size.intValue();
		boolean bool_exists =  file.exists();
		
		if(bool_exists){
			System.out.println(file);			
			InputStream stream = new FileInputStream(file);
			byte_file = new byte[int_size];
			stream.read(byte_file);
			stream.close();
		}
		
		Map map_file = new HashMap();
		map_file.put("str_docsol", str_docsol);
		map_file.put("str_internodocsol", str_internodocsol);
		map_file.put("byte_file", byte_file);
		map_file.put("mimeType", mimeType);
		map_file.put("int_size", new Integer(int_size));
		
		return map_file;
	}
}
